package com.xyz.keshe.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.xyz.keshe.db.DatabaseHelper;
import com.xyz.keshe.model.News;

import java.util.ArrayList;
import java.util.List;

public class NewsDao {
    private final DatabaseHelper dbHelper;

    public NewsDao(Context context) {
        this.dbHelper = new DatabaseHelper(context);
    }

    // 添加新闻
    public long addNews(News news) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.COLUMN_NEWS_TITLE, news.getTitle());
        values.put(DatabaseHelper.COLUMN_NEWS_CONTENT, news.getContent());
        values.put(DatabaseHelper.COLUMN_NEWS_AUTHOR, news.getAuthor());
        values.put(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID, news.getCategoryId());
        values.put(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT, 0); // 初始浏览量为0

        long id = db.insert(DatabaseHelper.TABLE_NEWS, null, values);
        db.close();
        return id;
    }

    // 更新新闻
    public int updateNews(News news) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.COLUMN_NEWS_TITLE, news.getTitle());
        values.put(DatabaseHelper.COLUMN_NEWS_CONTENT, news.getContent());
        values.put(DatabaseHelper.COLUMN_NEWS_AUTHOR, news.getAuthor());
        values.put(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID, news.getCategoryId());
        values.put(DatabaseHelper.COLUMN_UPDATE_TIME, "CURRENT_TIMESTAMP");

        int rowsAffected = db.update(
                DatabaseHelper.TABLE_NEWS,
                values,
                DatabaseHelper.COLUMN_ID + " = ?",
                new String[]{String.valueOf(news.getId())}
        );
        db.close();
        return rowsAffected;
    }

    // 删除新闻
    public void deleteNews(int id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.delete(
                DatabaseHelper.TABLE_NEWS,
                DatabaseHelper.COLUMN_ID + " = ?",
                new String[]{String.valueOf(id)}
        );
        db.close();
    }

    // 增加浏览量
    public void incrementViewCount(int newsId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("UPDATE " + DatabaseHelper.TABLE_NEWS + 
                   " SET " + DatabaseHelper.COLUMN_NEWS_VIEW_COUNT + " = " + 
                   DatabaseHelper.COLUMN_NEWS_VIEW_COUNT + " + 1 " +
                   "WHERE " + DatabaseHelper.COLUMN_ID + " = ?", 
                   new String[]{String.valueOf(newsId)});
        db.close();
    }

    // 获取所有新闻
    public List<News> getAllNews() {
        List<News> newsList = new ArrayList<>();
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " ORDER BY n." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC";

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                News news = new News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
                news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
                news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));

                newsList.add(news);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return newsList;
    }

    // 通过ID获取新闻
    public News getNewsById(int id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " WHERE n." + DatabaseHelper.COLUMN_ID + " = ?";
        
        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

        News news = null;
        if (cursor != null && cursor.moveToFirst()) {
            news = new News();
            news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
            news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
            news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
            news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
            news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
            news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
            news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
            news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
            news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));
            cursor.close();
        }
        db.close();
        return news;
    }

    // 通过栏目ID获取新闻列表
    public List<News> getNewsByCategoryId(int categoryId) {
        List<News> newsList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " WHERE n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = ? " +
                             "ORDER BY n." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC";
        
        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(categoryId)});

        if (cursor.moveToFirst()) {
            do {
                News news = new News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
                news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
                news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));

                newsList.add(news);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return newsList;
    }

    // 获取每个栏目最新的N条新闻
    public List<News> getLatestNewsByCategory(int limit) {
        List<News> newsList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " WHERE n." + DatabaseHelper.COLUMN_ID + " IN " +
                             "(SELECT n2." + DatabaseHelper.COLUMN_ID + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n2 " +
                             "WHERE n2." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID +
                             " ORDER BY n2." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC " +
                             "LIMIT " + limit + ") " +
                             "ORDER BY n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + ", n." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC";
        
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                News news = new News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
                news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
                news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));

                newsList.add(news);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return newsList;
    }

    // 通过关键词搜索新闻
    public List<News> searchNews(String keyword) {
        List<News> newsList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " WHERE n." + DatabaseHelper.COLUMN_NEWS_TITLE + " LIKE ? " +
                             "OR n." + DatabaseHelper.COLUMN_NEWS_CONTENT + " LIKE ? " +
                             "ORDER BY n." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC";
        
        String searchParam = "%" + keyword + "%";
        Cursor cursor = db.rawQuery(selectQuery, new String[]{searchParam, searchParam});

        if (cursor.moveToFirst()) {
            do {
                News news = new News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
                news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
                news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));

                newsList.add(news);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return newsList;
    }

    // 获取相关新闻（同类别的其他新闻）
    public List<News> getRelatedNews(int newsId, int categoryId, int limit) {
        List<News> newsList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        
        String selectQuery = "SELECT n.*, c." + DatabaseHelper.COLUMN_CATEGORY_NAME + 
                             " FROM " + DatabaseHelper.TABLE_NEWS + " n " +
                             "JOIN " + DatabaseHelper.TABLE_CATEGORY + " c " +
                             "ON n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = c." + DatabaseHelper.COLUMN_ID +
                             " WHERE n." + DatabaseHelper.COLUMN_NEWS_CATEGORY_ID + " = ? " +
                             "AND n." + DatabaseHelper.COLUMN_ID + " != ? " +
                             "ORDER BY n." + DatabaseHelper.COLUMN_CREATE_TIME + " DESC " +
                             "LIMIT " + limit;
        
        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(categoryId), String.valueOf(newsId)});

        if (cursor.moveToFirst()) {
            do {
                News news = new News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_TITLE)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CONTENT)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_AUTHOR)));
                news.setCategoryId(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_CATEGORY_ID)));
                news.setCategoryName(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CATEGORY_NAME)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.COLUMN_UPDATE_TIME)));

                newsList.add(news);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return newsList;
    }
} 